import base64
import datetime
import json
import os
import time
from io import BytesIO

import numpy as np
import openpyxl
import pandas as pd
# import modin.pandas as pd
import requests
from bs4 import BeautifulSoup
from Crypto.Cipher import AES
from django.forms.models import model_to_dict
from openpyxl.styles import Font, PatternFill
from PIL import Image
from pyecharts import options as opts
from pyecharts.charts import Bar, Tab

from jz.models import LoginInfo, Setting, TaskDayReport, TaskStates, TaskType

import warnings
warnings.filterwarnings('ignore') # 因为警告： UserWarning: Workbook contains no default style, apply openpyxl's default

#######################################################################################################
def get_proxy():
    proxy_db = model_to_dict(Setting.objects.get(key='proxy'))
    return {'http':proxy_db['value']} if proxy_db['value'] else None
def get_var(key):
    return model_to_dict(Setting.objects.get(key=key))['value'].replace('\\',os.sep)
# 转字典
def dtol(x):
    return [model_to_dict(i).values() for i in x]
# user_path = os.path.expanduser('~')
# report_config = Setting.objects.values()
def config_path():
    global CONFIG_SET
    CONFIG_SET = {}
    for i in Setting.objects.values_list():
        CONFIG_SET[i[0]] = i[1]
    CONFIG_SET['html_path'] = CONFIG_SET['temp_path'] + 'temp.html'
    CONFIG_SET['kh_path'] = CONFIG_SET['temp_path'] + '开户\\'
    CONFIG_SET['wh_path'] = CONFIG_SET['temp_path'] + '外呼\\'
    CONFIG_SET['kh_res_path'] = CONFIG_SET['temp_path'] + '狂撸已处理订单汇总表.xlsx'
    CONFIG_SET['jk_output'] = CONFIG_SET['temp_path'] + 'jk_output.xlsx'
    CONFIG_SET['jk_csv'] = CONFIG_SET['temp_path'] + 'jk.csv'
    return CONFIG_SET
config_path()
# 文件夹

#屏蔽报错 ： See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/
pd.set_option('mode.chained_assignment', None)

# html_path = user_path + model_to_dict(Setting.objects.get(key='html_path'))['value']
# gj_path = user_path + model_to_dict(Setting.objects.get(key='gj_path'))['value']
# temp_path = user_path + model_to_dict(Setting.objects.get(key='temp_path'))['value']
# kh_path = user_path + model_to_dict(Setting.objects.get(key='kh_path'))['value']
# wh_path = model_to_dict(Setting.objects.get(key='wh_path'))['value']
# rb_path = user_path + report_config['rb_path']
time_list = ['08','09','10','11','12','13','14','15','16','17']
team1_dic = {'中星-宜昌&襄阳':['上海航动科技有限公司（宜昌）','湖北航动（襄阳）'],'金音-宜昌&襄阳':['宜昌-航动3','湖北航动（襄阳02）'],'昊天-宜昌&襄阳':['襄阳-航动3','湖北航动_宜昌'],'鹤壁-宜昌&襄阳':['宜昌-航动4','襄阳-航动4']}

#######################################################################################################
#登陆取得令牌，存在数据库
def jz_login(city_id): # city_id-->access_token，JSESSIONID，used_team
    print(city_id,'==>验证登录是否还有效',end='==>')
    login_info = model_to_dict(LoginInfo.objects.get(key=city_id)) # 取出access_token，JSESSIONID，used_team 按照city_id
    req = requests.post("http://134.200.26.196/api/log/saveLog",headers={'access_token': login_info['access_token']},proxies=get_proxy()).json()# 如果有效则直接返回值，无效的话重新登陆并更新  
    if req["success"]:
        print('有效，复用↘')
        return login_info
    # 需要重新验证登录
    for i in range(2):
        print(f"无效,第{i+1}次尝试登录",end='==>')
        login_res, JSESSIONID, team_name = login(login_info['username'], login_info['password'])
        login_res = json.loads(login_res)
        if login_res["errorMessage"] != "验证码错误." and JSESSIONID != "notok":
            access_token = login_res['content']['token']['access_token']
            print("登陆成功↘")
            LoginInfo.objects.filter(key=city_id).update(access_token=access_token,jsessionid=JSESSIONID,team_name=team_name)
            return model_to_dict(LoginInfo.objects.get(key=city_id))
    return False

def pad(s):
    BS = AES.block_size
    return s + (BS - len(s) % BS) * chr(BS - len(s) % BS)
def encrypt(text, key):
    mode = AES.MODE_ECB
    text = pad(text).encode('utf-8')
    crypt = AES.new(key.encode('utf-8'), mode)
    cipher_text = crypt.encrypt(text)
    return base64.b64encode(cipher_text)
def get_xy(img):
  img_array = np.array(img)
  arr1 = img_array[:]
  for x in range(1,arr1.shape[0]):
      for y in range(1,arr1.shape[1]):
          a = img_array[x,y][0]
          b = img_array[x,y][1]
          c = img_array[x,y][2]
          arr1[x,y] = (0,0,c)
  rgb_date = np.argwhere(img_array == [255, 255, 255])
  i_temp = [0, 0, 0]
  num = 0
  for i in rgb_date:
      if i[0] == i_temp[0]:
          num = num + 1
      else:
          num = 0
      if num >= 7: 
          return (i[1] - 6)
      i_temp = i


def login(login_name,password):
    a = requests.get("http://134.200.26.196/api/captcha1/get?captchaType=blockPuzzle",proxies=get_proxy()) # 得到解析验证码需要用到json
    imgdata = base64.b64decode(json.loads(a.content)["repData"]["originalImageBase64"]) # 验证码图片base64解码
    x = get_xy(Image.open(BytesIO(imgdata))) # 找需要x轴需要偏移的量

    x = str(x)
    z = """{"x":%s,"y":5}"""%x

    secretKey = json.loads(a.content)["repData"]["secretKey"] # 得到密钥
    pointJson = encrypt(str(z), secretKey).decode('utf-8')  # 验证码偏移信息加密
    pointJson = pointJson.replace("+", "%2B")
    token = json.loads(a.content)["repData"]["token"] # 唯一令牌

    session_request = requests.session() # 需要check，跳过这一步会报错
    login_url = 'http://134.200.26.196/api/captcha1/check?captchaType=blockPuzzle&pointJson=%s&token=%s'%(pointJson,token)
    result = session_request.get(login_url,proxies=get_proxy())
    result.content.decode('utf-8')

    captchaVerification = encrypt(token + "---" + str(z), secretKey).decode('utf-8')
    key = requests.get('http://134.200.26.196/api/oauth/getKey',proxies=get_proxy())
    key = json.loads(key.content)['content']
    password = encrypt(password, key).decode('utf-8')
    session_request = requests.session()
    login_url = 'http://134.200.26.196/api/oauth/token'
    result = session_request.post(login_url,
                                data={
                                    'client_id': 'app_client',
                                    'client_secret': 'hollycrm_app_client',
                                    'username': login_name,
                                    'password': password,
                                    'grant_type': 'password',
                                    'scope': 'read write',
                                    'errorCnt': '0',
                                    'captcha_uid': '',
                                    'captcha': '',
                                    'deviceId': '',
                                    'loginType': '',
                                    'key': key,
                                    'captchaVerification': captchaVerification
                                },
                                headers = {
                                      'Accept': 'application/json, text/plain, */*',
                                      'Accept-Encoding': 'gzip, deflate',
                                      'Accept-Language': 'zh-CN,zh;q=0.9,en;q=0.8,en-GB;q=0.7,en-US;q=0.6',
                                      'Connection': 'keep-alive',
                                      'Content-Length': '250',
                                      'Content-Type': 'application/x-www-form-urlencoded',
                                      'Cookie': '',
                                      'Host': '134.200.26.196',
                                      'Referer': 'http://134.200.26.196/login',
                                      'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.87 Safari/537.36 Edg/80.0.361.50'
                                  },
                                  proxies=get_proxy()
                                )
    body_message = result.content.decode('utf-8')
    try:
        JSESSIONID = result.cookies.get_dict()['JSESSIONID']
        team_name = result.json()['content']['userInfo']['deptName'].split('－')[-1]
    except:
        print(body_message)
        JSESSIONID = "notok"
        team_name = ""
    return body_message, JSESSIONID, team_name

def jz_download_exl(city_id, exl_path='web', now_date=None, startTimeBegin='', startTimeEnd='',deptName='航动',taskName='',handleResult='',isConn='',remark='',up_task=False):
    if now_date:
        startTimeBegin = f'{now_date} 00:00:00'
        startTimeEnd = f'{now_date} 23:00:00'
        show_time = now_date
    elif startTimeBegin==None or startTimeEnd==None:
        print('请确定时间。')
        return False
    else:
        show_time = f'{startTimeBegin[:13]}～{startTimeEnd[:13]}'.replace(' ','T')
    if exl_path=='web':
        exl_path = CONFIG_SET['wh_path']
    res_login = jz_login(city_id)
    
    if up_task:
        head = {
            "Accept": "application/json, text/plain, */*",
            "Accept-Encoding": "gzip, deflate",
            "Accept-Language": "zh-CN,zh;q=0.9,en-US;q=0.8,en;q=0.7",
            "access_token": res_login['access_token'],
            "Connection": "keep-alive",
            "Cookie": f"JSESSIONID={res_login['jsessionid']}",
            "Host": "134.200.26.196",
            "Referer": "http://134.200.26.196/workplace",
            "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.114 Safari/537.36 Edg/89.0.774.68"
            }
        ress = requests.get('http://134.200.26.196/api/taskInfo/showTaskList?task_status=running&rows=50', headers=head,proxies=get_proxy()).json()
        ress = ress['content']['rows']

        # TaskStates.objects.filter(cityId=city_id).delete()
        for i in ress:
            t = TaskStates(cityId=city_id, taskName=i['taskName'], deptName=i['deptName'], createTime=i['createTime'], totalNum=str(i['totalNum']), finishNum=str(i['finishNum']), leftNum=str(i['totalNum'] - i['finishNum']),per=format(i['finishNum'] / i['totalNum'], '.2%'),upDate=str(datetime.datetime.today())[:19])
            t.save()

    aka = res_login['city_name']+res_login['used_team']
    headers = {
        'Host': '134.200.26.196',
        'Connection': 'keep-alive',
        'Content-Length': '265',
        'Accept': 'application/json, text/plain, */*',
        'Origin': 'http://134.200.26.196',
        'access_token': res_login['access_token'],
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.74 Safari/537.36 Edg/79.0.309.43',
        'Content-Type': 'application/x-www-form-urlencoded',
        'Referer': 'http://134.200.26.196/callrecord/callrecordNew',
        'Accept-Encoding': 'gzip, deflate',
        'Accept-Language': 'zh-CN,zh;q=0.9,en;q=0.8,en-GB;q=0.7,en-US;q=0.6',
        'Cookie': 'JSESSIONID=' + res_login['jsessionid']
    }

    params = {
        'deptName': deptName,#团队名称(支持模糊)
        'projectName': '',
        'taskName': taskName,
        'city': '',
        'userCode': '',
        'handleUser': '',
        'startTimeBegin': startTimeBegin,
        'startTimeEnd': startTimeEnd,
        'endTimeBegin': '',
        'endTimeEnd': '',
        'handleResult': handleResult,
        'isConn': isConn,#已接通=1
        'mobileNo': '',
        'page': '1',
        'rows': '10',
        'tenantId': '',
        'called': '',
        'qualityCondition': ''
    }
    print(f'{aka}开始下载',end='==>')
    req = requests.post("http://134.200.26.196/api/callRecord/download", headers=headers, data=params,proxies=get_proxy())
    if b"false" in req.content[0:100]:
        print("下载错误", req.content[0:100])
        return False
    print(f'{aka}结束')
    # now_time = str(datetime.datetime.now())[:19]s
    # now_time = now_time.replace(":", "$")
    if remark:
        file_name = remark+'.xlsx'
    else:
        file_name = f"{show_time}+{city_id}+{aka}.xlsx"
    with open(exl_path + file_name, 'wb') as file:
        file.write(req.content)
    exl_size = os.path.getsize(exl_path + file_name)
    exl_size = '%.1f' % float(exl_size/1000000) + 'MB'
    return {"exl_path": exl_path, "file_name": file_name, "exl_size": exl_size, "city_id":city_id, "remark":remark}

def jz_download_zn(city_id, now_date):
    startTimeBegin = f'{now_date} 06:00:00'
    startTimeEnd = (datetime.datetime.strptime(now_date,'%Y-%m-%d')+datetime.timedelta(days=1)).strftime('%Y-%m-%d 06:00:00')
    show_time = now_date

    res_login = jz_login(city_id)
    aka = res_login['city_name']+res_login['used_team']+'智能'
    headers = {
        'Host': '134.200.26.196',
        'Connection': 'keep-alive',
        'Content-Length': '265',
        'Accept': 'application/json, text/plain, */*',
        'Origin': 'http://134.200.26.196',
        'access_token': res_login['access_token'],
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.74 Safari/537.36 Edg/79.0.309.43',
        'Content-Type': 'application/x-www-form-urlencoded',
        'Referer': 'http://134.200.26.196/callrecord/callrecordNew',
        'Accept-Encoding': 'gzip, deflate',
        'Accept-Language': 'zh-CN,zh;q=0.9,en;q=0.8,en-GB;q=0.7,en-US;q=0.6',
        'Cookie': 'JSESSIONID=' + res_login['jsessionid']
    }

    params = {
        'deptName': '',#团队名称(支持模糊)
        'taskName': '',
        'city': '',
        'handleUser': '',
        'startTimeBegin': startTimeBegin,
        'startTimeEnd': startTimeEnd,
        'mobileNo': '',
        'page': '1',
        'rows': '10',
        'tenantId': '',
        'called': '',
        'qualityCondition': ''
    }
    print(f'{aka}开始下载',end='==>')
    req = requests.post("http://134.200.26.196/hollycas-smartcall/smartCall/record/download", headers=headers, data=params,proxies=get_proxy())
    if b"false" in req.content[0:100]:
        print("下载错误", req.content[0:100])
        return False
    print(f'{aka}结束')
    # now_time = str(datetime.datetime.now())[:19]s
    # now_time = now_time.replace(":", "wh$")

    exl_path = CONFIG_SET['wh_path']
    file_name = f"{show_time}+{city_id}+{aka}.xlsx"
    with open(exl_path + file_name, 'wb') as file:
        file.write(req.content)
    exl_size = os.path.getsize(exl_path + file_name)
    exl_size = '%.1f' % float(exl_size/1000000) + 'MB'
    return {"exl_path": exl_path, "file_name": file_name, "exl_size": exl_size, "city_id":city_id}
# 撸开户
def open_excel(excel_file):
    try:
        wb = openpyxl.load_workbook(filename=excel_file.replace("\\","/"))
        return wb
    except:
        print("open excel file failed!")
def Group_By_Excel_2_Dict(fp):
    zero_date = 0
    # 将订单表按日期:任务:订单状态:开户细则 进行分组统计;fp 文件所在路径；返回4级字典列表
    GE_Dict_list = [] # 用列表装各个字典
    for root, dirs, files in os.walk(fp):  # 漫游目录fp,将fp目录下Excel表全部读到列表
        for file in files:
            print(file)
            excel_file = os.path.join(root, file).replace('\\', '/')
            wb = open_excel(excel_file)  # 打开excel文件
            sheets = wb.sheetnames       # 获取所有sheet表名
            print(sheets)
            for sh in sheets:
                ws = wb[sh]              # 打开每一张sheet表
                find_items = ['任务', '开户状态', '开户失败原因', '通话时间']  # 定位关键字
                items_location = dict.fromkeys(find_items)                     # 关键字列表转为字典，作为键
                # 标准表头判断
                head_list = [ws.cell(1, col1).value for col1 in range(1, ws.max_column + 1)]
                if set(find_items).issubset(set(head_list)):  # 判断需要定位的关键字是否为标准表头中的字项目
                    # 关键字段定位
                    for j in range(1, ws.max_column + 1):                          # 到每个sheet中的第一行找关键字，定位（行数，列数），作为键值
                        if ws.cell(1, j).value in find_items:
                            items_location[ws.cell(1, j).value] = [1, j]
                    col_task = int(items_location['任务'][1])    # 取（行数，列数）中的第二个列数
                    col_orderstatus = int(items_location['开户状态'][1])
                    col_failreason = int(items_location['开户失败原因'][1])
                    col_calltime = int(items_location['通话时间'][1])
                    col_product = col_orderstatus-2 if "产品" in ws.cell(1,col_orderstatus-2).value else 4  # 定位产品列
                    # 将迭代器中的值转换为以行为单位的列表
                    rowslist = [] #预定义二维列表
                    for row in ws.rows:
                        rowlist = []
                        for cell in row:
                            rowlist.append(cell.value)  # 构造一维
                        rowslist.append(rowlist)  # 构造二维
                    # 关键字段取值
                    particular_word = {}
                    for i in range(1, ws.max_row):                              # 第一行是标题名，对应表中的字段名所以应该从第二行开始
                        task = "未知任务" if str(rowslist[i][col_task - 1]) == "None" or str(rowslist[i][col_task - 1]) == "" else rowslist[i][col_task - 1].replace('\n', '').replace('\r', '')
                        orderstatus = "未处理" if str(rowslist[i][col_orderstatus - 1]) not in ["开通", "未开通", "已被开通", "质检不合格", "作废"] else rowslist[i][col_orderstatus - 1].replace('\n', '').replace('\r', '')
                        failreason = rowslist[i][col_failreason - 1] if str(rowslist[i][col_failreason - 1]) == "None" or str(rowslist[i][col_failreason - 1]) == "" or isinstance(rowslist[i][col_failreason - 1],int) else rowslist[i][col_failreason - 1].replace('\n', '').replace('\r', '') #去掉所有的回车符和换行符
                        # print(str(failreason))  #发现有真空白存在
                        calltime = "0000-00-00" if str(rowslist[i][col_calltime - 1]) == "None" or str(rowslist[i][col_calltime - 1]) == "" or type(rowslist[i][col_calltime - 1]) != str else  rowslist[i][col_calltime - 1].split(" ")[0]  # 日期转为 “2020-11-15” 的格式,有未知时间直接手动标记
                        if calltime == "0000-00-00":
                            zero_date += 1
                        product = "未知产品" if str(rowslist[i][col_product - 1]) == "None" or str(rowslist[i][col_product - 1]) == "" else str(rowslist[i][col_product - 1])
                        if calltime.split(" ")[0] and task and orderstatus :    # 关键字段非空
                            # 开户状态剖析-开通、未开通、已被开通、作废、质检不合格：orderstatus，对应原因不明、失败原因：failreason
                            # 对开通状态的失败原因中无特别说明的进行填充："正常开通"，"原因不明" 构造4级字典
                            if str(failreason) == "None" or str(failreason) == "":
                                if orderstatus == "开通":
                                    # 判断是否已经有此条字典记录
                                    if calltime in particular_word and task in particular_word[calltime] and orderstatus in particular_word[calltime][task] and "正常开通" in particular_word[calltime][task][orderstatus]:
                                        # 有的话记录值更新 +1
                                        particular_word[calltime][task][orderstatus]["正常开通"] += 1
                                    else:
                                        # 没有的话，新增字典记录，初始化值为1
                                        particular_word.setdefault(calltime, {}).setdefault(task, {}).setdefault(orderstatus, {}).setdefault("正常开通", 1)
                                        # particular_word[calltime][task][orderstatus]["正常开通"].value= 1 int型无value方法，直接创建的时候初始化为1
                                else:
                                    if calltime in particular_word and task in particular_word[calltime] and orderstatus in particular_word[calltime][task] and "原因不明" in particular_word[calltime][task][orderstatus]:
                                        particular_word[calltime][task][orderstatus]["原因不明"] += 1
                                    else:
                                        particular_word.setdefault(calltime, {}).setdefault(task, {}).setdefault(orderstatus, {}).setdefault("原因不明", 1)
                            else:
                                if calltime in particular_word and task in particular_word[calltime] and orderstatus in particular_word[calltime][task] and failreason in particular_word[calltime][task][orderstatus]:
                                    particular_word[calltime][task][orderstatus][failreason] += 1
                                else:
                                    particular_word.setdefault(calltime, {}).setdefault(task, {}).setdefault(orderstatus,{}).setdefault(failreason, 1)
                                #************统计半停机状态*****************#
                                if "停" in str(failreason):
                                    if calltime in particular_word and task in particular_word[calltime] and "停机" in particular_word[calltime][task] :
                                        particular_word[calltime][task]["停机"] += 1
                                    else:
                                        particular_word.setdefault(calltime, {}).setdefault(task, {}).setdefault("停机",1)

                            # 统计已开通的产品明细***************************************2020-11-22 - 23增加补充部分*****************#
                            if orderstatus == "开通":
                                if calltime in particular_word and task in particular_word[calltime] and "开通产品" in particular_word[calltime][task] and product in particular_word[calltime][task]["开通产品"]:
                                    particular_word[calltime][task]["开通产品"][product] += 1
                                else:
                                    particular_word.setdefault(calltime, {}).setdefault(task, {}).setdefault("开通产品",{}).setdefault(product, 1)
                                #*************混叠产品统计***************# *标记1*
                                if "5G" in product or "5g" in product:
                                    if calltime in particular_word and task in particular_word[calltime] and "5G产品" in particular_word[calltime][task] :
                                        particular_word[calltime][task]["5G产品"] += 1
                                    else:
                                        particular_word.setdefault(calltime, {}).setdefault(task, {}).setdefault("5G产品", 1)
                                if "5G会员权益" in product :
                                    if calltime in particular_word and task in particular_word[calltime] and "5G超会&5G尊享" in particular_word[calltime][task] :
                                        particular_word[calltime][task]["5G超会&5G尊享"] += 1
                                    else:
                                        particular_word.setdefault(calltime, {}).setdefault(task, {}).setdefault("5G超会&5G尊享", 1)
                                if "日租宝5G会员" in product :
                                    if calltime in particular_word and task in particular_word[calltime] and "5G尝鲜包" in particular_word[calltime][task] :
                                        particular_word[calltime][task]["5G尝鲜包"] += 1
                                    else:
                                        particular_word.setdefault(calltime, {}).setdefault(task, {}).setdefault("5G尝鲜包", 1)
                                if "王卡会员" in product:
                                    if calltime in particular_word and task in particular_word[calltime] and "王卡会员" in particular_word[calltime][task] :
                                        particular_word[calltime][task]["王卡会员"] += 1
                                    else:
                                        particular_word.setdefault(calltime, {}).setdefault(task, {}).setdefault("王卡会员", 1)
                                if "回馈礼包" in product or "回馈" in product:
                                    if calltime in particular_word and task in particular_word[calltime] and "回馈礼包" in particular_word[calltime][task] :
                                        particular_word[calltime][task]["回馈礼包"] += 1
                                    else:
                                        particular_word.setdefault(calltime, {}).setdefault(task, {}).setdefault("回馈礼包", 1)
                            # 强行增加键值对 "开通率","订单总量","开通总量","已被开通量","质检不合格量","作废量","未开通总量",
                            particular_word.setdefault(calltime, {}).setdefault(task, {}).setdefault("开通率", 0.00)
                            particular_word.setdefault(calltime, {}).setdefault(task, {}).setdefault("订单总量", 0)
                            particular_word.setdefault(calltime, {}).setdefault(task, {}).setdefault("开通总量", 0)
                            particular_word.setdefault(calltime, {}).setdefault(task, {}).setdefault("已被开通量", 0)
                            particular_word.setdefault(calltime, {}).setdefault(task, {}).setdefault("质检不合格量", 0)
                            particular_word.setdefault(calltime, {}).setdefault(task, {}).setdefault("作废量", 0)
                            particular_word.setdefault(calltime, {}).setdefault(task, {}).setdefault("未开通总量", 0)
                            particular_word.setdefault(calltime, {}).setdefault(task, {}).setdefault("未处理量", 0)
                        else:
                            pass
                        # GE_Dict.update(particular_word)
                    GE_Dict_list.append(particular_word)
    print (f'=======共{zero_date}个未匹配时间=======')
    for GE_Dict in GE_Dict_list:  # 字典结构为此列表中的子项
        for key1, value1 in GE_Dict.items():  # {日期:任务}
            for key2, value2 in value1.items():       # {任务:订单状态-大类};{任务:开通率}
                ddsum = 0
                ktsum = 0
                bktsum = 0
                zjsum = 0
                zfsum = 0
                wktsum = 0
                wclsum = 0
                for key3, value3 in value2.items():   # {订单状态:未开原因-细分};{开通率:初始化值0.00}
                    # 剔除非开通状态的统计指标键名 *标记2*
                    if key3 not in ["开通率","订单总量","开通总量","已被开通量","质检不合格量","作废量","未开通总量","未处理量", "开通产品","5G产品","5G超会&5G尊享","5G尝鲜包","王卡会员","回馈礼包","停机"] :     # 剔除统计指标项         # 排除统计指标
                        for key4, value4 in value3.items():   # {未开原因-细分:量化值}
                            ddsum += value4
                            if key3 == "开通":
                                ktsum += value4
                            elif key3 == "已被开通":
                                bktsum += value4
                            elif key3 == "质检不合格":
                                zjsum += value4
                            elif key3 == "作废":
                                zfsum += value4
                            elif key3 == "未开通":
                                wktsum += value4
                            elif key3 == "未处理":
                                wclsum += value4
                            else:
                                pass
                    else:
                        pass
                # print(key1,"\t",key2,"\t",value2["订单总量"],"\t",ddsum)
                # 根据字典键修改值
                GE_Dict[key1][key2]["开通率"] = round(ktsum / ddsum, 4)   # 到key2这个范围
                GE_Dict[key1][key2]["订单总量"] = ddsum
                GE_Dict[key1][key2]["开通总量"] = ktsum
                GE_Dict[key1][key2]["已被开通量"] = bktsum
                GE_Dict[key1][key2]["质检不合格量"] = zjsum
                GE_Dict[key1][key2]["作废量"] = zfsum
                GE_Dict[key1][key2]["未开通总量"] = wktsum
                GE_Dict[key1][key2]["未处理量"] = wclsum
    return GE_Dict_list
def l_kh():
    fp=CONFIG_SET['kh_path']
    out_fileName=CONFIG_SET['kh_res_path']
    res_list = Group_By_Excel_2_Dict(fp)
    rowslist = []  # 预定义二维列表
    for res in res_list:
        for key_d, val_t in res.items():
            for key_t, val_item in val_t.items():
                # 按时间-任务，展开的一维列表，构成一行数据记录 *标记3*
                failreason = val_item["未开通"] if "未开通" in val_item else "" # 可能有没有未开通的存在
                failreason2str = str(failreason).replace("{", "").replace("}", "")
                productlist = val_item["开通产品"] if "开通产品" in val_item else ""
                product2str = str(productlist).replace("{", "").replace("}", "")
                product5g = val_item["5G产品"] if "5G产品" in val_item else ""
                product5ghy = val_item["5G超会&5G尊享"] if "5G超会&5G尊享" in val_item else ""
                product5gcx = val_item["5G尝鲜包"] if "5G尝鲜包" in val_item else ""
                productwk = val_item["王卡会员"] if "王卡会员" in val_item else ""
                producthk = val_item["回馈礼包"] if "回馈礼包" in val_item else ""
                failreasontj = val_item["停机"] if "停机" in val_item else ""
                rowlist = [key_d, key_t, val_item["订单总量"], val_item["开通总量"],
                           val_item["开通率"],  val_item["未处理量"], val_item["已被开通量"], val_item["质检不合格量"], val_item["作废量"],
                           val_item["未开通总量"],
                           failreason2str,product2str,product5g,product5ghy,product5gcx,productwk,producthk,failreasontj]
                rowslist.append(rowlist) # 一维追加到二维 re.escape(failreason2str).encode("utf-8")
    # print(rowslist)
    N_YMD = "1120"
    # 写入到表
    # out_fileName = os.path.join(of + "\\" + "狂撸已处理订单汇总表" + ".xlsx")
    # 准备表
    wb = openpyxl.Workbook()
    # 默认表sheet1
    ws1 = wb.active
    # 更改表名
    ws1.title = '开户统计'
    # 准备表头 *标记4*
    headoftable = ['订单日期', '任务名称', '需处理订单总量', '开通量', '开通率', '已导出未处理订单量', '已被开通量', '质检不合格量', '作废量', '未开通量', '未开通原因', "开通产品","5G产品", "5G超会&5G尊享", "5G尝鲜包", "王卡会员", "回馈礼包","停机"]
    # 写入表头
    for i in range(0,len(headoftable)):
        ws1.cell(row=1, column=i + 1).value = headoftable[i]
        # 表头分段美化
        if i in [0, 1]:
            ws1.cell(row=1, column=i + 1).font = Font(bold=True)
            ws1.cell(row=1, column=i + 1).fill = PatternFill(fill_type="solid", start_color="FFFF6600",
                                                             end_color="FFFFFFFF")
        elif i in [2, 3]:
            ws1.cell(row=1, column=i + 1).font = Font(bold=True)
            ws1.cell(row=1, column=i + 1).fill = PatternFill(fill_type="solid", start_color="FF66CCFF",
                                                             end_color="FFFFFFFF")
        elif i in [4, 11]:
            ws1.cell(row=1, column=i + 1).font = Font(bold=True)
            ws1.cell(row=1, column=i + 1).fill = PatternFill(fill_type="solid", start_color="FF00FF00",
                                                             end_color="FFFFFFFF")
        elif i in [5, 6, 7, 8]:
            ws1.cell(row=1, column=i + 1).font = Font(bold=True)
            ws1.cell(row=1, column=i + 1).fill = PatternFill(fill_type="solid", start_color="FFFF6699",
                                                             end_color="FFFFFFFF")
        elif i in [9, 10]:
            ws1.cell(row=1, column=i + 1).font = Font(bold=True)
            ws1.cell(row=1, column=i + 1).fill = PatternFill(fill_type="solid", start_color="FF6699FF",
                                                             end_color="FFFFFFFF")
        elif i in [12, 13, 14, 15, 16]:
            ws1.cell(row=1, column=i + 1).font = Font(bold=True)
            ws1.cell(row=1, column=i + 1).fill = PatternFill(fill_type="solid", start_color="FFCC00FF",
                                                             end_color="FFFFFFFF")
        elif i in [17]:
            ws1.cell(row=1, column=i + 1).font = Font(bold=True)
            ws1.cell(row=1, column=i + 1).fill = PatternFill(fill_type="solid", start_color="FF6699FF",
                                                             end_color="FFFFFFFF")
        else:
            pass
    # 写入数据
    for r in range(0,len(rowslist)):
        for c in range(0, len(rowslist[r])):
            # 从第二行第一列开始写入,构造小表，统计总外呼情况
            ws1.cell(row=r + 2, column=c + 1).value = rowslist[r][c]
            if c == 4:
                if rowslist[r][c]: #判断非空
                    ws1.cell(row=r + 2, column=c + 1).value = str(round(rowslist[r][c]*100,2))+"%"
                    if float(rowslist[r][c]) < 0.900:  #成功率阈值颜色标记--硬阈值
                        ws1.cell(row=r + 2, column=c + 1).font = Font(bold = False, color = "FFFF0033")
    filename = out_fileName.replace('\\', '/')
    wb.save(filename=filename)
    return filename

def updateKhToDayreport():
    fs = pd.read_excel(CONFIG_SET['kh_res_path'])
    dd = int(fs['订单日期'].max().split('-')[1])
    fs = fs.set_index(['订单日期','任务名称'])
    for i in ['宜昌','襄阳']:
        path = get_var('rb_path')%(i,dd)
        wb = openpyxl.load_workbook(path)
        sheet2 = wb['项目明细']

        for i in range(2, 200):
            if sheet2[f'A{i}'].value:
                try:
                    sheet2[f'G{i}'] = fs.loc[[(sheet2[f'A{i}'].value,sheet2[f'C{i}'].value)]]['开通量'][0]
                except:
                    continue
        wb.save(path)

def get_tasktype(taskname, typedic):
    for text_key in typedic:
        if text_key in taskname:
            return typedic[text_key]
    return '未知'

def output_xl(now_date, city_ids:list, city_name, input_path=None,wh_path=None,output_path=None, is_zn=False, is_temp=False): # 填表
    wh_path = CONFIG_SET['wh_path']
    input_path = get_var('rb_path')
    dd = int(now_date.split('-')[1])
    report_path = input_path%(city_name,dd)
    # report_path = input_path
    if output_path==None:
        output_path = report_path
    if is_temp:
        output_path = get_var('rb_temp_path')%(city_name,dd)
    # 处理人工部分
    filenames = []
    for city_id in city_ids:
        res = jz_download_exl(city_id, wh_path, now_date)
        filename = res['exl_path'] + res['file_name']
        filenames.append(filename)
    res_lu = l_jz(filenames,is_list=True)

    type_dic = {}
    for i in TaskType.objects.values_list('key','textKey'):
        if i[1]:
            for ii in i[1].split(','):
                type_dic[ii] = i[0]

    # print(res_lu)
    print('开始写入表格',end='==>')
    wb = openpyxl.load_workbook(report_path) 
    sheet1 = wb['外呼一览']
    sheet2 = wb['项目明细']
    # sheet3 = wb['智能外呼']
    where_dic = {'日期': 'A',
        '任务': 'C',
        '拨打量': 'D',
        '接通': 'E',
        '成功': 'F',
        '有效接通':'L'}
    for i in range(1, 200):
        if sheet2[f'A{i}'].value == now_date:
            print("\n\n\n日期冲突请检查！！！\n日期冲突请检查！！！\n日期冲突请检查！！！")
            break
        if sheet2[f'A{i}'].value is None:
            for index_name in res_lu:
                if index_name in where_dic:
                    num = 0
                    for val in res_lu[index_name]:
                        if index_name == '任务':
                            sheet2[f'B{i+num}'] = get_tasktype(val,type_dic)
                        sheet2[f'{where_dic[index_name]}{i+num}'] = val
                        num = num + 1
            break

    sheet1['C3'] = now_date

    # 智能外呼部分
    if is_zn:
        res = jz_download_zn(city_id, now_date)
        filename = res['exl_path'] + res['file_name']
        res_lu = l_zn(filename)
        print('开始写入智能数据',end='==>')
        sheet3 = wb['智能外呼']
        where_dic = {'日期': 'A',
            '任务': 'B',
            '拨打量': 'C',
            '接通': 'D',
            '30秒接通': 'E',
            '意向量': 'F'}
        for i in range(1, 200):
            if sheet3[f'A{i}'].value == now_date:
                print("\n\n\n日期冲突请检查！！！\n日期冲突请检查！！！\n日期冲突请检查！！！")
                break
            if sheet3[f'A{i}'].value is None:
                for index_name in res_lu:
                    if index_name in where_dic:
                        num = 0
                        for val in res_lu[index_name]:
                            sheet3[f'{where_dic[index_name]}{i+num}'] = val
                            num = num + 1
                break

    wb.save(output_path)
    
    # print('开始写入数据库',end='==>')
    # input_day_report(res_lu)
    # print('完毕') 

def l_jz(l, to_html=False, is_list=False):
    if is_list:
        print(f'开始合并',end='==>')
        fs = pd.DataFrame()
        for file_path in l:
            try:
                fs1 = pd.read_excel(file_path)
                # utime = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(os.path.getmtime(file_path)))
                fs = pd.concat((fs, fs1))
            except:
                pass
        if fs.empty:
            return "文件为空~"
        print(f'合并成功，开始分析',end='==>')
    else:
        fs = pd.read_excel(l)
    fs["日期"] = fs["通话时间"].apply(lambda x: x[:10])
    groupby_v = ["日期","部门","任务"]
    cc_count1 = fs.groupby(groupby_v).count()["被叫"] # 拨打量(分项目)
    c_count1 = fs[(fs["通话时长(秒)"]>0)&(fs['处理人工号'].notnull())].groupby(groupby_v).count()["被叫"] # 大于0秒的数量(分项目)
    s_count1 = fs[fs["用户意向"]=="成功"].groupby(groupby_v).count()["被叫"] # 用户意向成功的数量(分项目)
    users_count = fs.groupby(groupby_v)['处理人'].nunique() # 执行坐席数量(分项目)
    c_count2 = fs[fs["通话时长(秒)"]>=30].groupby(groupby_v).count()["被叫"] # 大于30秒的数量(分项目)
    df1 = pd.concat([cc_count1,c_count1,s_count1,users_count,c_count2], axis=1)  # 按列索引合并
    # df1 = pd.merge(c_count1,s_count1,how = 'left',on='任务')
    # df1.loc[('','','合计')] = df1.apply(lambda x: x.sum())  # 加上合计行
    df1.reset_index(inplace=True)
    df1.columns=['日期','部门','任务','拨打量','接通','成功','坐席数','有效接通']
    df1['接通率'] = df1['接通'] / df1['拨打量']
    df1['接通率'] = df1['接通率'].apply(lambda x: format(x, '.2%'))
    df1['成功率'] = df1['成功'] / df1['接通']
    df1['成功率'] = df1['成功率'].apply(lambda x: format(x, '.2%'))       
    df1 = df1[df1["成功"].notnull()]
    df1[['接通']] = df1[['接通']].fillna(0).astype(int)
    df1[['成功']] = df1[['成功']].fillna(0).astype(int)
    # if to_html:
    #     print(f'开始写入html文件',end='==>')
    #     df1.columns=[time.strftime("%H:%M:%S", time.localtime()),'部门','任务','拨打量','接通','成功','坐席数','接通率','成功率']
    #     df1.to_html("..\\index.html", index=None)
    #     print(f'写入成功。')
    if to_html:
        df1.columns=[time.strftime("%H:%M:%S", time.localtime()),'部门','任务','拨打量','接通','成功','坐席数','有效接通','接通率','成功率']
        return df1
    df1.columns=['日期','部门','任务','拨打量','接通','成功','坐席数','有效接通','接通率','成功率']
    return df1

def l_zn(l, is_list=False):
    if is_list:
        print(f'开始合并',end='==>')
        fs = pd.DataFrame()
        for file_path in l:
            try:
                fs1 = pd.read_excel(file_path)
                # utime = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(os.path.getmtime(file_path)))
                fs = pd.concat((fs, fs1))
            except:
                pass
        if fs.empty:
            return "文件为空~"
        print(f'合并成功，开始分析',end='==>')
    else:
        fs = pd.read_excel(l)
    fs["日期"] = fs["开始时间"].apply(lambda x: x[:10])
    groupby_v = ["日期","任务"]
    cc_count1 = fs.groupby(groupby_v).count()["被叫"] # 拨打量(分项目)
    c_count1 = fs[(fs["通话时长(秒)"]>0)&(fs['处理人工号'].notnull())].groupby(groupby_v).count()["被叫"] # 大于0秒的数量(分项目)
    c_count2 = fs[fs["通话时长(秒)"]>=30].groupby(groupby_v).count()["被叫"] # 大于等于30秒的数量(分项目)
    s_count1 = fs[fs["呼叫状态"]=="通话成功"].groupby(groupby_v).count()["被叫"] # 用户意向成功的数量(分项目)
    df1 = pd.concat([cc_count1,c_count1,c_count2,s_count1], axis=1)  # 按列索引合并
    # df1 = pd.merge(c_count1,s_count1,how = 'left',on='任务')
    # df1.loc[('','','合计')] = df1.apply(lambda x: x.sum())  # 加上合计行
    df1.reset_index(inplace=True)
    df1.columns=['日期','任务','拨打量','接通','30秒接通','意向量']
    # df1['接通率'] = df1['接通'] / df1['拨打量']
    # df1['接通率'] = df1['接通率'].apply(lambda x: format(x, '.2%'))
    # df1['意向率'] = df1['意向量'] / df1['接通']
    # df1['意向率'] = df1['意向率'].apply(lambda x: format(x, '.2%'))
    # df1.columns=['日期','任务','拨打量','接通','30秒接通','意向量','接通率','意向率']
    return df1

def jiankong(now_date,city_ids=[]): # 监控
    paths = []
    for city_id in city_ids:
        path = jz_download_exl(city_id, CONFIG_SET['wh_path'], now_date=now_date)
        paths.append(path['exl_path']+path['file_name'])
    df1 = l_jz(paths, is_list=True, to_html=True)
    df1.to_excel(CONFIG_SET['jk_output'], index=None)
    # df1.to_csv('downloads/jk_output.csv')
    return 'ok'
    # input_day_report(res_lu)#写入数据库

def ljz_from_csv(): # 监控
    try:
        fs = pd.read_csv(CONFIG_SET['jk_csv'],low_memory=False)
        fs["日期"] = fs["通话时间"].apply(lambda x: x[:10])
        groupby_v = ["日期","部门","任务"]
        cc_count1 = fs.groupby(groupby_v).count()["被叫"] # 拨打量(分项目)
        c_count1 = fs[(fs["通话时长(秒)"]>0)&(fs['处理人工号'].notnull())].groupby(groupby_v).count()["被叫"] # 大于0秒的数量(分项目)
        s_count1 = fs[fs["用户意向"]=="成功"].groupby(groupby_v).count()["被叫"] # 用户意向成功的数量(分项目)
        users_count = fs.groupby(groupby_v)['处理人'].nunique() # 执行坐席数量(分项目)
        df1 = pd.concat([cc_count1,c_count1,s_count1,users_count], axis=1)  # 按列索引合并
        # df1 = pd.merge(c_count1,s_count1,how = 'left',on='任务')
        # df1.loc[('','','合计')] = df1.apply(lambda x: x.sum())  # 加上合计行
        df1.reset_index(inplace=True)
        df1.columns=['日期','部门','任务','拨打量','接通','成功','坐席数']
        df1['接通率'] = df1['接通'] / df1['拨打量']
        df1['接通率'] = df1['接通率'].apply(lambda x: format(x, '.2%'))
        df1['成功率'] = df1['成功'] / df1['接通']
        df1['成功率'] = df1['成功率'].apply(lambda x: format(x, '.2%'))
        df1 = df1[df1["成功"].notnull()]
        df1.columns=[time.strftime("%H:%M:%S", time.localtime()),'部门','任务','拨打量','接通','成功','坐席数','接通率','成功率']
        df1['剩余数据'] = df1['任务'].apply(lambda x: search_task_left(x))
        
        # df1.columns=['日期','部门','任务','拨打量','接通','成功','坐席数','接通率','成功率']
        c_count2 = fs[(fs["通话时长(秒)"]>0)&(fs['处理人工号'].notnull())].groupby(["任务", "处理人"]).count()["被叫"]
        s_count2 = fs[fs["用户意向"]=="成功"].groupby(["任务", "处理人"]).count()["被叫"]        
        df2 = pd.merge(c_count2,s_count2,how = 'left',on=["任务", "处理人"])
        df2.columns=['接通','成功']
        df2['成功率'] = df2['成功'] / df2['接通']
        df2['成功率'] = df2['成功率'].apply(lambda x: format(x, '.2%'))
        with pd.ExcelWriter(CONFIG_SET['jk_output']) as writer:
            df1.to_excel(writer, sheet_name='Sheet1', index=None)
            df2.to_excel(writer, sheet_name='处理人')
        df1.to_csv('downloads/jk_output.csv',index=None,encoding='GB2312')
    except:
        pd.DataFrame().to_csv('downloads/jk_output.csv',index=None)
    return 'ok'

def search_task(city_id):
    type_dic = {}
    for i in TaskType.objects.values_list('key','textKey'):
        if i[1]:
            for ii in i[1].split(','):
                type_dic[ii] = i[0]

    res_login = jz_login(city_id)

    head = {
        "Accept": "application/json, text/plain, */*",
        "Accept-Encoding": "gzip, deflate",
        "Accept-Language": "zh-CN,zh;q=0.9,en-US;q=0.8,en;q=0.7",
        "access_token": res_login['access_token'],
        "Connection": "keep-alive",
        "Cookie": f"JSESSIONID={res_login['jsessionid']}",
        "Host": "134.200.26.196",
        "Referer": "http://134.200.26.196/workplace",
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.114 Safari/537.36 Edg/89.0.774.68"
        }
    ress = requests.get('http://134.200.26.196/api/taskInfo/showTaskList?task_status=running&rows=50', headers=head,proxies=get_proxy()).json()
    ress = ress['content']['rows']

    ret_res = []
    for i in ress:
        a = i['taskName']
        # b = i['deptName']
        # if b is None:
        b = city_id
        c = i['createTime']
        d = i['totalNum']
        e = i['finishNum']
        f = d - e
        g = format(e / d, '.2%')
        h = get_tasktype(a,type_dic)
        if h == '':
            print('未识别：',i['taskName'])
        ret_res.append([a,b,c,d,e,f,g,h])

    return ret_res

def search_phone(city_id,phone,startdate,enddate,handleResult,taskName,deptName,isConn):
    res_login = jz_login(city_id)
    if enddate != '':
        enddate = f'{enddate}+23:00:00'
    header = {
        "Accept": "application/json, text/plain, */*",
        "Accept-Encoding": "gzip, deflate",
        "Accept-Language": "zh-CN,zh;q=0.9,en;q=0.8,en-GB;q=0.7,en-US;q=0.6",
        "access_token": res_login['access_token'],
        "Connection": "keep-alive",
        "Cookie": f"JSESSIONID={res_login['jsessionid']}",
        "Host": "134.200.26.196",
        "Referer": "http://134.200.26.196/callrecord/callrecordNew",
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.114 Safari/537.36 Edg/89.0.774.68"
        }
    header_get = f"http://134.200.26.196/api/callRecord/showRecordList?handleUser=&handleResult={handleResult}&startTimeBegin={startdate}+00:00:00&startTimeEnd={enddate}&endTimeBegin=&endTimeEnd=&isConn={isConn}&taskName={taskName}&qualityResult=&productId=&projectName=&called=&userCode=&mobileNo={phone}&city=&qualityCondition=&callType=&deptName={deptName}&parentDeptName=&calling=&page=1&rows=10"
    res = requests.get(header_get,headers=header,proxies=get_proxy()).json()
    return res

def html_to_gj():
    html_path = CONFIG_SET['html_path']
    fg = open(html_path, "r", encoding="utf-8")
    html = fg.read()
    soup = BeautifulSoup(html, "lxml")

    city_dic = {
        '武汉':'D',	'孝感':'E',	'江汉':'F',	'咸宁':'G',	'黄冈':'H',	'黄石':'I',	'鄂州':'J',
        '襄阳':'K',	'荆州':'L',	'荆门':'M',	'宜昌':'N',	'恩施':'O',	'十堰':'P',	'随州':'Q'

    }


    wb = openpyxl.load_workbook(CONFIG_SET['gj_path'])
    show_date = soup.find(class_='txt').text
    fs = wb['样表-工作日']
    fs1 = wb.copy_worksheet(wb['样表-工作日'])
    fs1.title = show_date[-2:]
    def get_num(data_id, num):
        ss = soup.findAll(attrs={'data-id':data_id})
        for i in ss:
            if '号码维度' in i.text:
                city_name = i['data-name']
                city_name = city_name.replace('仙桃/潜江/天门', '江汉')
                y = city_dic[city_name]
                city_num = i.find(class_='number').get_text()
                city_num = city_num.replace(',','')
                city_num = int(city_num)
                fs1[f'{y}{num}'] = city_num
                # print(i['data-name'],end='')
                # print(i.find(class_='number').get_text())
    num = 3
    for i in ('alarm5','alarm6','alarm12','alarm13','alarm14','alarm8','alarm9','alarm10','alarm11','alarm22Num','alarm32','alarm33'):
        get_num(i, num)
        num = num + 1
    fs1['A1'] = f'2I异常订单日通报（{show_date}）'
    wb.save(CONFIG_SET['gj_path'])

def user_num(x,y) -> Bar:
    c = (
        Bar()
        .add_xaxis(x)
        .add_yaxis("商家A", )
        .set_global_opts(
            title_opts=opts.TitleOpts(title="Bar-DataZoom（slider-水平）"),
            datazoom_opts=[opts.DataZoomOpts()],
        )
    )
def upcallreport(now_date, city_id_list=get_var(key='jk_cityid_list').split(','),is_all=False): #更新外呼数据,存至jk.csv
    city_id_list=get_var(key='jk_cityid_list').split(',')
    jk_csv_path = CONFIG_SET['jk_csv'] #获取jk.csv的路径
    try:
        fs = pd.read_csv(jk_csv_path,low_memory=False) #如果能读取到文件则>fs
    except:
        fs = pd.DataFrame() #如果读取不到则创建DataFrame>pd
    today_mintime = f'{now_date} 00:00:00' 
    if is_all:
        fs = pd.DataFrame()
        startTimeBegin = today_mintime
    else:
        if fs.empty:
            startTimeBegin = today_mintime
        else:
            fs_maxtime = fs['通话时间'].max()
            if fs_maxtime[:10] == now_date:
                startTimeBegin = (datetime.datetime.strptime(fs_maxtime,'%Y-%m-%d %H:%M:%S')-datetime.timedelta(minutes=20)).strftime('%Y-%m-%d %H:%M:%S')
            else:
                fs = pd.DataFrame()
                startTimeBegin = today_mintime
    print('监控日期：'+startTimeBegin+' 开始！')
    path_list = []  
    TaskStates.objects.all().delete()
    for city_id in city_id_list:  
        path = jz_download_exl(city_id,startTimeBegin=startTimeBegin,remark=f'jk-{city_id}',up_task=True)
        path_list.append(path['exl_path']+path['file_name'])

    for path in path_list:
        try:
            fs1 = pd.read_excel(path, index_col=None)
            fs = pd.concat([fs, fs1],ignore_index=True)
            os.remove(path)
        except:
            print(path, '出错了~  size：','%.1f' % float(os.path.getsize(path)/1000) + 'KB')
            os.remove(path)
            # os.rename(path, path+'出错.xlsx')
    if fs.empty:
        print('貌似没有外呼~')
        fs.to_csv(jk_csv_path, index=None)
        max_time = task_state = ''
    else:
        fs.sort_values(['通话时间','通话时长(秒)'],inplace=True)
        fs.drop_duplicates(['被叫','通话时间'],keep='last',inplace=True)
        # fs[fs.duplicated(['被叫','通话时间'],keep=False)].sort_values('通话时长(秒)').drop_duplicates(['被叫','通话时间'],keep='last',inplace=True) # 去重
        fs.to_csv(jk_csv_path, index=None)
        task_state = ''
        for task_name in fs['任务'].unique().tolist():
            task_state += str(task_name) + '\n# 外呼数量：' + str(fs[fs['任务']==task_name].count()['被叫']) + ' # 最新外呼时间：' + str(fs[fs['任务']==task_name]['通话时间'].max()) + '\n\n'
        max_time = fs['通话时间'].max()

    # 生成外呼情况表格
    type_dic = {}
    for i in TaskType.objects.values_list('key','textKey'):
        if i[1]:
            for ii in i[1].split(','):
                type_dic[ii] = i[0]
    res_data = pd.DataFrame(list(TaskStates.objects.values()))#.to_excel('./downloads/task.xlsx',index=None)
    res_data['type'] = res_data['taskName'].apply(lambda x: get_tasktype(x,type_dic))
    res_data.to_csv('./downloads/task.csv',index=None)
    return {'update_time':f'更新时间：{str(datetime.datetime.now())[:19]}<br>最新外呼时间：{max_time}','task_state':task_state}

    # fs["日期"] = fs["通话时间"].apply(lambda x: x[:10])
    # fs["时段"] = fs["通话时间"].apply(lambda x: x[11:13])
    # groupby_v = ["日期","部门","任务"]
    # cc_count1 = fs.groupby(groupby_v).count()["被叫"] # 拨打量(分项目)
    # c_count1 = fs[fs["通话时长(秒)"]>0].groupby(groupby_v).count()["被叫"] # 大于0秒的数量(分项目)
    # s_count1 = fs[fs["用户意向"]=="成功"].groupby(groupby_v).count()["被叫"] # 用户意向成功的数量(分项目)
    # users_count = fs.groupby(groupby_v)['处理人'].nunique() # 执行坐席数量(分项目)
    # df1 = pd.concat([cc_count1,c_count1,s_count1,users_count], axis=1)  # 按列索引合并
    # # df1 = pd.merge(c_count1,s_count1,how = 'left',on='任务')
    # # df1.loc[('','','合计')] = df1.apply(lambda x: x.sum())  # 加上合计行
    # df1.reset_index(inplace=True)
    # df1.columns=['日期','部门','任务','拨打量','接通','成功','坐席数']
    # df1['接通率'] = df1['接通'] / df1['拨打量']
    # df1['接通率'] = df1['接通率'].apply(lambda x: format(x, '.2%'))
    # df1['成功率'] = df1['成功'] / df1['接通']
    # df1['成功率'] = df1['成功率'].apply(lambda x: format(x, '.2%'))       
    # df1 = df1[df1["成功"].notnull()]

    # df1.columns=[time.strftime("%H:%M:%S", time.localtime()),'部门','任务','拨打量','接通','成功','坐席数','接通率','成功率']
    # return df1
# jk('2021-11-26')

def upcallreport1(now_date, city_id_list=get_var(key='jk_cityid_list').split(','),is_all=False): #更新外呼数据,存至jk.csv
    city_id_list=get_var(key='jk_cityid_list').split(',')
    jk_csv_path = CONFIG_SET['jk_csv'] #获取jk.csv的路径
    fs = pd.DataFrame() #如果读取不到则创建DataFrame>pd

    startTimeBegin = f'{now_date} 00:00:00'

    print('监控日期：'+startTimeBegin+' 开始！')
    path_list = []  
    TaskStates.objects.all().delete()
    for city_id in city_id_list:  
        path = jz_download_exl(city_id,startTimeBegin=startTimeBegin,remark=f'jk-{city_id}',up_task=True)
        path_list.append(path['exl_path']+path['file_name'])

    for path in path_list:
        try:
            fs1 = pd.read_excel(path, index_col=None)
            fs = pd.concat([fs, fs1],ignore_index=True)
            os.remove(path)
        except:
            exl_size = float(os.path.getsize(path)/1000)
            if exl_size > 3:
                print(path, '!!!出错了~  size：','%.1f' % exl_size + 'KB')
            # os.rename(path, path+'出错.xlsx')
    if fs.empty:
        print('貌似没有外呼~')
        fs.to_csv(jk_csv_path, index=None)
        max_time = task_state = ''
    else:
        fs.to_csv(jk_csv_path, index=None)
        task_state = ''
        # for task_name in fs['任务'].unique().tolist():
        #     task_state += str(task_name) + '\n# 外呼数量：' + str(fs[fs['任务']==task_name].count()['被叫']) + ' # 最新外呼时间：' + str(fs[fs['任务']==task_name]['通话时间'].max()) + '\n\n'
        max_time = fs['通话时间'].max()

    # 生成外呼情况表格
    type_dic = {}
    for i in TaskType.objects.values_list('key','textKey'):
        if i[1]:
            for ii in i[1].split(','):
                type_dic[ii] = i[0]
    res_data = pd.DataFrame(list(TaskStates.objects.values()))#.to_excel('./downloads/task.xlsx',index=None)
    res_data['type'] = res_data['taskName'].apply(lambda x: get_tasktype(x,type_dic))
    res_data.to_csv('./downloads/task.csv',index=None)

    try:
        fs["日期"] = fs["通话时间"].apply(lambda x: x[:10])
        groupby_v = ["日期","部门","任务"]
        cc_count1 = fs.groupby(groupby_v).count()["被叫"] # 拨打量(分项目)
        c_count1 = fs[(fs["通话时长(秒)"]>0)&(fs['处理人工号'].notnull())].groupby(groupby_v).count()["被叫"] # 大于0秒的数量(分项目)
        s_count1 = fs[fs["用户意向"]=="成功"].groupby(groupby_v).count()["被叫"] # 用户意向成功的数量(分项目)
        users_count = fs.groupby(groupby_v)['处理人'].nunique() # 执行坐席数量(分项目)
        df1 = pd.concat([cc_count1,c_count1,s_count1,users_count], axis=1)  # 按列索引合并
        # df1 = pd.merge(c_count1,s_count1,how = 'left',on='任务')
        # df1.loc[('','','合计')] = df1.apply(lambda x: x.sum())  # 加上合计行
        df1.reset_index(inplace=True)
        df1.columns=['日期','部门','任务','拨打量','接通','成功','坐席数']
        df1['接通率'] = df1['接通'] / df1['拨打量']
        df1['接通率'] = df1['接通率'].apply(lambda x: format(x, '.2%'))
        df1['成功率'] = df1['成功'] / df1['接通']
        df1['成功率'] = df1['成功率'].apply(lambda x: format(x, '.2%'))
        df1 = df1[df1["成功"].notnull()]
        df1.columns=[time.strftime("%H:%M:%S", time.localtime()),'部门','任务','拨打量','接通','成功','坐席数','接通率','成功率']
        df1['剩余数据'] = df1['任务'].apply(lambda x: search_task_left(x))
        
        # df1.columns=['日期','部门','任务','拨打量','接通','成功','坐席数','接通率','成功率']
        c_count2 = fs[(fs["通话时长(秒)"]>0)&(fs['处理人工号'].notnull())].groupby(["任务", "处理人"]).count()["被叫"]
        s_count2 = fs[fs["用户意向"]=="成功"].groupby(["任务", "处理人"]).count()["被叫"]        
        df2 = pd.merge(c_count2,s_count2,how = 'left',on=["任务", "处理人"])
        df2.columns=['接通','成功']
        df2['成功率'] = df2['成功'] / df2['接通']
        df2['成功率'] = df2['成功率'].apply(lambda x: format(x, '.2%'))
        with pd.ExcelWriter(CONFIG_SET['jk_output']) as writer:
            df1.to_excel(writer, sheet_name='Sheet1', index=None)
            df2.to_excel(writer, sheet_name='处理人')
        df1.to_csv('downloads/jk_output.csv',index=None,encoding='GB2312')
    except:
        pd.DataFrame().to_csv('downloads/jk_output.csv',index=None)

    team_dic = {'上海航动科技有限公司（宜昌）':'宜昌', '宜昌-航动3':'宜昌', '宜昌-航动4':'宜昌', '湖北航动（襄阳）':'襄阳', '湖北航动（襄阳02）':'襄阳', '襄阳-航动3':'襄阳', '湖北航动_宜昌':'宜昌', '襄阳-航动4':'襄阳'}
    
    fs = fs[fs["任务"].notnull()]
    fs["日期"] = fs["通话时间"].apply(lambda x: x[:10])
    fs["时段"] = fs["通话时间"].apply(lambda x: x[11:13])
    fs["地市"] = fs["部门"].apply(lambda x: team_dic[x])
    # time_list = ['08','09','10','11','12','13','14','15','16','17']
    # theme = ThemeType.DARK
    page = Page(layout=Page.DraggablePageLayout)
    for i in create_pyecharts_table(fs):
        page.add(i)
    page.add(
        create_pyecharts_user(time_list,fs),
        create_pyecharts_success(time_list,fs)
        )
    page.render('./templates/render.html')

    return {'update_time':f'更新时间：{str(datetime.datetime.now())[:19]}<br>最新外呼时间：{max_time}','task_state':task_state}


def up_task(key=None):
    if key == 'up':
        city_id_list=get_var(key='jk_cityid_list').split(',')
        res = []
        type_dic = {}
        for i in TaskType.objects.values_list('key','textKey'):
            if i[1]:
                for ii in i[1].split(','):
                    type_dic[ii] = i[0]
        for city_id in city_id_list:
            res_login = jz_login(city_id)
            head = {
                "Accept": "application/json, text/plain, */*",
                "Accept-Encoding": "gzip, deflate",
                "Accept-Language": "zh-CN,zh;q=0.9,en-US;q=0.8,en;q=0.7",
                "access_token": res_login['access_token'],
                "Connection": "keep-alive",
                "Cookie": f"JSESSIONID={res_login['jsessionid']}",
                "Host": "134.200.26.196",
                "Referer": "http://134.200.26.196/workplace",
                "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.114 Safari/537.36 Edg/89.0.774.68"
                }
            ress = requests.get('http://134.200.26.196/api/taskInfo/showTaskList?task_status=running&rows=50', headers=head,proxies=get_proxy()).json()
            ress = ress['content']['rows']

            # TaskStates.objects.filter(cityId=city_id).delete()
            for i in ress:
                res.append( {
                    'city_name': res_login['city_name'],
                    'used_team': res_login['used_team'],
                    'cityId': city_id,
                    'taskName': i['taskName'],
                    'deptName': i['deptName'],
                    'createTime': i['createTime'],
                    'totalNum': str(i['totalNum']),
                    'finishNum': str(i['finishNum']),
                    'leftNum': str(i['totalNum'] - i['finishNum']),
                    'per': format(i['finishNum'] / i['totalNum'], '.2%'),
                    'upDate': str(datetime.datetime.today())[:19],
                    'type': get_tasktype(i['taskName'], type_dic)
                } )
        with open('./downloads/task.json', 'w') as result_file:
            json.dump(res, result_file, indent=4, ensure_ascii=False)
        return res
    with open('./downloads/task.json', 'r') as result_file:
        res = json.load(result_file)
    return res

def jk_zn(city_id, now_date):
    res = jz_download_zn(city_id, now_date)
    filename = res['exl_path'] + res['file_name']
    res_lu = l_zn(filename)
    print('开始写入智能数据',end='==>')
    res_lu.to_csv('./downloads/jk_zn.csv',index=None)
    return './downloads/jk_zn.csv'

from pyecharts import options as opts
from pyecharts.charts import Bar, Grid, Line, Page, Scatter, Tab
from pyecharts.components import Table

# from pyecharts.options import ComponentTitleOpts,InitOpts
# from pyecharts.globals import ThemeType

def create_pyecharts_user(x,data):#
    df_user_count = data.groupby(['时段','地市'])['处理人工号'].nunique()
    df_user_count_1 = data.groupby(['时段','部门'])['处理人工号'].nunique()
    city_list = data['地市'].unique().tolist()
    team_list = data['部门'].unique().tolist()
    bar = (
        Bar()
        .add_xaxis(x)
        .set_global_opts(
            title_opts=opts.TitleOpts(title='人力分布-团队/地市'),
        )
    )
    for team_name in team_list:
        y_data = []
        for time_1 in time_list:
            try:
                y_data.append(int(df_user_count_1.loc[(time_1,team_name)]))
            except:
                y_data.append(0)
        bar.add_yaxis(team_name, y_data, stack=team_name,label_opts=opts.LabelOpts(is_show=0))#label_opts=opts.LabelOpts(is_show=0)
    line = Line()
    line .add_xaxis(x)
    for city_name in city_list:
        y_data = []
        for time_1 in time_list:
            try:
                y_data.append(int(df_user_count.loc[(time_1,city_name)]))
            except:
                y_data.append(0)
        line .add_yaxis(city_name, y_data, 
            linestyle_opts=opts.LineStyleOpts(width=2), 
            is_smooth=True)
    bar.overlap(line)
    return bar

def create_pyecharts_success(x,data):
    grid = Grid()
    df_user_count = data.groupby(['时段','任务'])['处理人工号'].nunique()
    task_list = data['任务'].unique().tolist()
    c_count1 = data[data["通话时长(秒)"]>0].groupby(['时段','任务']).count()["被叫"] # 大于0秒的数量(分项目)
    s_count1 = data[data["用户意向"]=="成功"].groupby(['时段','任务']).count()["被叫"] # 用户意向成功的数量(分项目)
    df = pd.concat([c_count1,s_count1], axis=1)  # 按列索引合并
    # df.reset_index(inplace=True)
    df.columns=['接通','成功']
    df = df[df["成功"].notnull()]
    df['成功率'] = df['成功'] / df['接通'] * 100

    bar = Bar()#成功量
    bar.add_xaxis(x).set_global_opts(title_opts=opts.TitleOpts(title='成功量分布-任务'),
    legend_opts=opts.LegendOpts(type_="scroll", pos_left="right", orient="vertical"),)
    bar.extend_axis(yaxis=opts.AxisOpts(axislabel_opts=opts.LabelOpts(formatter="{value} %"),interval=5))
    line = Line()#成功率
    line.add_xaxis(x)
    scatter = Scatter()#人数
    scatter.add_xaxis(x)
    scatter.set_global_opts(visualmap_opts=opts.VisualMapOpts(type_="size", max_=150, min_=20),)

    for task_name in task_list:#成功量y成功率y
        suc_data = []
        suc1_data = []
        for time_1 in time_list:
            try:
                suc_data.append(int(df.loc[(time_1,task_name)]['成功']))
            except:
                suc_data.append(0)
            try:
                suc1_data.append(int(df.loc[(time_1,task_name)]['成功率']))
            except:
                suc1_data.append(0)
        scatter.add_yaxis(task_name, suc_data,is_selected=0,color="green")
        line.add_yaxis(task_name, suc1_data, 
            linestyle_opts=opts.LineStyleOpts(width=2), 
            is_smooth=True, is_selected=0,yaxis_index=1)

    for task_name in task_list:
        y_data = []
        for time_1 in time_list:
            try:
                y_data.append(int(df_user_count.loc[(time_1,task_name)]))
            except:
                y_data.append(0)
        bar.add_yaxis(task_name, y_data, is_selected=0, bar_width="5px")

    bar.overlap(line)
    bar.overlap(scatter)
    grid.add(bar,grid_opts=opts.GridOpts(pos_top="50%"),is_control_axis_index=True)
    return grid

def search_task_left(taskName):
    # 查询任务剩余数据量，如查不到则返回“未知”
    try:
        res = model_to_dict(TaskStates.objects.get(taskName=taskName))['leftNum']
        return res
    except:
        return "未知"

def create_pyecharts_table(fs):
    groupby_v = ["日期","部门","任务"]
    cc_count1 = fs.groupby(groupby_v).count()["被叫"] # 拨打量(分项目)
    c_count1 = fs[(fs["通话时长(秒)"]>0)&(fs['处理人工号'].notnull())].groupby(groupby_v).count()["被叫"] # 大于0秒的数量(分项目)
    s_count1 = fs[fs["用户意向"]=="成功"].groupby(groupby_v).count()["被叫"] # 用户意向成功的数量(分项目)
    users_count = fs.groupby(groupby_v)['处理人'].nunique() # 执行坐席数量(分项目)
    df1 = pd.concat([cc_count1,c_count1,s_count1,users_count], axis=1)  # 按列索引合并
    # df1 = pd.merge(c_count1,s_count1,how = 'left',on='任务')
    # df1.loc[('','','合计')] = df1.apply(lambda x: x.sum())  # 加上合计行
    df1.reset_index(inplace=True)
    df1.columns=['日期','部门','任务','拨打量','接通','成功','坐席数']
    df1['接通率'] = df1['接通'] / df1['拨打量']
    df1['接通率'] = df1['接通率'].apply(lambda x: format(x, '.2%'))
    df1['成功率'] = df1['成功'] / df1['接通']
    df1['成功率'] = df1['成功率'].apply(lambda x: format(x, '.2%'))       
    df1 = df1[df1["成功"].notnull()]
    df1.columns=['日期','部门','任务','拨打量','接通','成功','坐席数','接通率','成功率']
    df1[['接通']] = df1[['接通']].astype(int)
    df1[['成功']] = df1[['成功']].astype(int)
    res = []
    for team1_name in team1_dic:
        df2 = df1[df1['部门'].isin(team1_dic[team1_name])]
        if df2.empty is not True:
            max_calltime = fs[fs['部门'].isin(team1_dic[team1_name])]['通话时间'].max()
            df2['剩余数据'] = df2['任务'].apply(lambda x: search_task_left(x))
            df2.loc['合计'] = ['合计','','',df2['拨打量'].sum(),df2['接通'].sum(),df2['成功'].sum(),'','','','']
            table = Table()
            headers = df2.columns.to_list()
            rows = df2.values.tolist()
            table.add(headers, rows)
            table.set_global_opts({"title":f"效能-{team1_name} 生成时间：{str(datetime.datetime.today())[:19]}", 
                            "subtitle":f'最后通话时间：{max_calltime}',
                            "title_style":"style='color:red'",
                            "subtitle_style":"style='color:green'"
                            })
            res.append(table)
    return res
def up_echarts():
    team_dic = {'上海航动科技有限公司（宜昌）':'宜昌', '宜昌-航动3':'宜昌', '宜昌-航动4':'宜昌', '湖北航动（襄阳）':'襄阳', '湖北航动（襄阳02）':'襄阳', '襄阳-航动3':'襄阳', '湖北航动_宜昌':'宜昌', '襄阳-航动4':'襄阳'}
    
    fs = pd.read_csv('./downloads/jk.csv',low_memory=False)
    fs = fs[fs["任务"].notnull()]
    fs["日期"] = fs["通话时间"].apply(lambda x: x[:10])
    fs["时段"] = fs["通话时间"].apply(lambda x: x[11:13])
    fs["地市"] = fs["部门"].apply(lambda x: team_dic[x])
    # time_list = ['08','09','10','11','12','13','14','15','16','17']
    # theme = ThemeType.DARK
    page = Page(layout=Page.DraggablePageLayout)
    for i in create_pyecharts_table(fs):
        page.add(i)
    page.add(
        create_pyecharts_user(time_list,fs),
        create_pyecharts_success(time_list,fs)
        )
    page.render('./templates/render.html')

def update_lx (city_name, m):
    exl_path = get_var(key=f'{city_name}电话购')
    path = get_var('rb_path')%(city_name,m)
    # path = r'C:\Users\fei\Desktop\航动文件\报表\电话外呼营销日报表\宜昌联通电话外呼营销日报表-湖北话加2022年4月.xlsx'
    fs = pd.read_excel(exl_path, header=1)
    username_dic = {'宜昌':'HB781807', '襄阳':'18627777741'}
    fs['电话购工号'] = fs['电话购工号'].astype('str')
    # fs.to_csv('downloads/1.csv')
    fs = fs[fs['电话购工号']==username_dic[city_name]]
    wb = openpyxl.load_workbook(path)
    sheet1 = wb['电话购情况']
    num = 2
    for i in range(1,65):
        date_ob = sheet1[f'A{num}'].value
        if date_ob == '' or date_ob is None:
            break
        if date_ob[0] != '2':
            pass
        else:
            sheet1[f'G{num}'] = fs[fs['订单时间'].str.contains(date_ob)].count()['订单号']
            sheet1[f'I{num}'] = fs[(fs['订单时间'].str.contains(date_ob)) & (fs['首充金额'] >= 50)].count()['订单号']
            sheet1[f'K{num}'] = fs[(fs['订单时间'].str.contains(date_ob)) & (fs['激活状态'] == '已激活')].count()['订单号']
        num = num + 1
    wb.save(path)